﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using Microsoft.Win32;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data;

namespace StudentModule.Views
{
    /// <summary>
    /// Interaction logic for ImportVanBangView.xaml
    /// </summary>
    public partial class ImportVanBangView : UserControl
    {
        VLUSMEntities entities = new VLUSMEntities();
        public ImportVanBangView()
        {
            InitializeComponent();
        }

        private void rbtbrowse_Click(object sender, RoutedEventArgs e)
        {
            OpenFileDialog dlg = new OpenFileDialog();
            dlg.DefaultExt = ".xls";
            dlg.Filter = "Excel File (.xls)|*.xls";
            if (dlg.ShowDialog() == true)
            {
                tblink.Text = dlg.FileName;
                //duong dan cua file
            }
        }

        private void rbtimport_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                // Lấy đường dẫn file excel
                string path = tblink.Text;

                // Tạo connection tới file excel
                string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);

                OleDbCommand command;



                using (OleDbConnection connection =
                             new OleDbConnection(excelConnectionString))
                {
                    connection.Open();

                    command = new OleDbCommand
                    ("Select*FROM[Sheet1$]", connection);
                    Import(command);

                    MessageBox.Show("Import thành công");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void Import(OleDbCommand command)
        {
            // Create DbDataReader to Data Worksheet
            using (DbDataReader dr = command.ExecuteReader())
            {
                DataTable dt = new DataTable();
                dt.Load(dr);
                foreach (DataRow row in dt.Rows)
                {
                    //DVHCCap1 tinh = new DVHCCap1();
                    //tinh.MaDonVi = row["Ma"].ToString();
                    //tinh.Ten = row["Tinh"].ToString();
                    //string str = row["Tinh"].ToString();
                    //DVHCCap1 tinh2 = entities.DVHCCap1.FirstOrDefault(c => c.Ten== str);
                    //DVHCCap2 quan = new DVHCCap2();
                    //quan.Cap1ID =tinh2.ID ;
                    //quan.Ten = row["Huyen"].ToString();
                    //quan.MaDonVi = row["MaHuyen"].ToString();
                    //entities.DVHCCap2.AddObject(quan);

                    string str = row["Mã sinh viên"].ToString();
                    SinhVien sv = entities.SinhViens.FirstOrDefault(c => c.MaSinhVien == str);
                    str = row["Văn bằng chứng chỉ"].ToString();
                    LoaiVanBang vanbang = entities.LoaiVanBangs.FirstOrDefault(c => c.TenGoi == str);
                    if (sv != null && vanbang != null)
                    {
                        VanBangDuocNhan vanbangduocnhan = entities.VanBangDuocNhans.FirstOrDefault(c => c.LoaiVanBangID == vanbang.ID && c.SinhVienID == sv.ID);
                        if (vanbangduocnhan != null)
                        {
                            vanbangduocnhan.NgayNhan = row["Ngày nhận"].ToString();
                            vanbangduocnhan.MoTa = row["Mô tả"].ToString();
                            vanbangduocnhan.DonViQuyetDinh = row["Đơn vị quyết định"].ToString();
                        }
                        else
                        {
                            VanBangDuocNhan newvanbangduocnhan = new VanBangDuocNhan();
                            newvanbangduocnhan.SinhVienID = sv.ID;
                            newvanbangduocnhan.LoaiVanBangID = vanbang.ID;
                            newvanbangduocnhan.NgayNhan = row["Ngày nhận"].ToString();
                            newvanbangduocnhan.MoTa = row["Mô tả"].ToString();
                            newvanbangduocnhan.DonViQuyetDinh = row["Đơn vị quyết định"].ToString();
                            entities.VanBangDuocNhans.AddObject(newvanbangduocnhan);
                        }
                    }



                    entities.SaveChanges();
                }
            }
        }

    }
}
